Php SQL PDO->Fetch() issue?

" /> prepare('DELETE FROM users WHERE id=:id') PDO will give you an instance of PDOStatement that you can PDO::bindParam() values to and execute. So let's do that and execute $statement->bindParam(':id', $id); $statement->execute() Now some behind the scenes magic happens here. PDO sends the data to MySQL.

MySQL examines the data and inserts into the prepared statement. By knowing where the data was supposed to be placed and how long the inserted data was, MySQL can determine the character ranges in a query that don't need execute (read: the data). So, when a hacker tries an SQL injection, MySQL doesn't even worry about evaluating anything that is bound to the prepared statement PDO says to MySQL The data for :id is ' OR 1=1 MySQL finds the location where :id was in the prepared statement.

(In this example, character 28) MySQL counts the length of the data (In this case, 11 characters) MySQL does 1st grade math and remembers that it should treat everything from character 28 to 39 as characters MySQL inserts the data and now the query looks like this: DELETE FROM users WHERE id=' OR 1=1 However, because it knows the position of the data, MySQL only analyzes everything outside of the pipes (|) for commands and keywords DELETE FROM users WHERE id=|' OR 1=1 So the text between the pipes never actually gets analyzed. When MySQL needs compare id's it still compares id's in the table with the data, but since the data was never executed, the SQL injection fails A Better Explanation of How PDO Prevents SQL Injections When we prepare a statement with PDO, it notifies the database of the upcoming query and where the data will be in the query. When we bind data to that query and execute it the database does some behind the scenes work to make sure that SQL injections are thwarted Let's take this behind the scenes work to another context.

You manage a PHP blog whose engine you wrote entirely by yourself. You are proud of the clever comment system you wrote until some jerk decides to post this comment: script type="text/javascript"> alert('You just been H4X0RED!1 LOLS'); When the script kiddie wakes up at 3 AM and sneaks down to his computer to try the code again htmlspecialchars() turns his lame attempt at humor into a jibberish mess. The function takes any character that is important in HTML (namely the and ) and turns them into their literal value ( and ).

The result is stored in the $result array, not the PDO object $one. This also needs an echo if you're not going to use shorttags. "> I'd use shorttags, so if you PHP setup has them enabled, but they are being deprecated in PHP 5.6: "> You can use shorttags for now, and I will continue to use them to protest the removal of this incredibly useful feature.

Deprecating shorttags will break a lot of WP themes and simple template engines! You should also consider using prepared statements. Without them, this script is vulnerable to SQL injections.

Prepare("SELECT * FROM contactgroups WHERE id=:id"); if( $query->execute(array(':id' => $_GET'id')) ) { $result = $query->fetch();? > " /> How PDO Prevents SQL Injection (too long to put in a comment - scroll down to see a better explanation) Let's begin with a query: mysql_query("DELETE FROM users WHERE id='". $id."'"); If $id = "' OR 1=1 --"; then the query would look like this when sent to MySQL (-- signifies the start of a comment): DELETE FROM users WHERE id='' OR 1=1 --' Obviously, the destruction that would follow could be catastrophic and possibly unreversible (unless you've got some smart DB admins).

The fix here instead of using the lengthy, mysql_real_escape_string() (I really never understood why the function name was so wordy in the first place), we can now use PDO prepared statements. By PDO::preparing() a statement you are sending a message to your DB telling it to store and optimize this query because it will be used later. Your DB stores an optimized query, taking careful note of where the data belongs.

$statement = $pdo->prepare('DELETE FROM users WHERE id=:id'); PDO will give you an instance of PDOStatement that you can PDO::bindParam() values to and execute. So let's do that and execute. $statement->bindParam(':id', $id); $statement->execute(); Now some behind the scenes magic happens here.

PDO sends the data to MySQL. MySQL examines the data and inserts into the prepared statement.By knowing where the data was supposed to be placed and how long the inserted data was, MySQL can determine the character ranges in a query that don't need execute (read: the data). So, when a hacker tries an SQL injection, MySQL doesn't even worry about evaluating anything that is bound to the prepared statement.

PDO says to MySQL, "The data for :id is ' OR 1=1 --" MySQL finds the location where :id was in the prepared statement.(In this example, character 28) MySQL counts the length of the data (In this case, 11 characters) MySQL does 1st grade math and remembers that it should treat everything from character 28 to 39 as characters. MySQL inserts the data and now the query looks like this: DELETE FROM users WHERE id=' OR 1=1 -- However, because it knows the position of the data, MySQL only analyzes everything outside of the pipes (|) for commands and keywords. DELETE FROM users WHERE id=|' OR 1=1 --| So the text between the pipes never actually gets analyzed.

When MySQL needs compare id's it still compares id's in the table with the data, but since the data was never executed, the SQL injection fails. A Better Explanation of How PDO Prevents SQL Injections When we prepare a statement with PDO, it notifies the database of the upcoming query and where the data will be in the query. When we bind data to that query and execute it the database does some behind the scenes work to make sure that SQL injections are thwarted.

Let's take this behind the scenes work to another context. You manage a PHP blog whose engine you wrote entirely by yourself. You are proud of the clever comment system you wrote until some jerk decides to post this comment: After you yell some four letter words at the computer screen and make sure that the script kiddie's parents never let him on the internet again, you solve the XSS vulnerability in your code with htmlspecialchars().

$comment_text = htmlspecialchars($_POST'comment_text'); Now what have you done here? When the script kiddie wakes up at 3 AM and sneaks down to his computer to try the code again, htmlspecialchars() turns his lame attempt at humor into a jibberish mess. The function takes any character that is important in HTML (namely the ) and turns them into their literal value ().

The HTML parser in everyone's browser interprets the < not as the beginning of an HTML tag, but as a sign to actually output the character This is essentially what the database engine does with all data inputted into prepared statements. Except since in SQL letters make up valid commands (and also valid data), the engine interprets all characters in the data as their literal value. So instead of: DELETE FROM users WHERE id = 0 OR 1=1 --' It evaluates each character in the data as it's literal value.In HTML that would be: DELETE FROM users WHERE id = 0 OR 1=1 --' If you look at both here, they both output the same thing, except in the second, the 'data' is being interpreted as it's literal value by the parser and not it's functional value.

The SQL does the same thing. By using the literal value of the data, none of the actual data can be interpreted as a command or part of one.

Thanks for your help! Especially the prepared statements bit. I am still struggling to understand how this can protect the query but that's just me (even I have read the link).

Do you mind if I ask for a little explanation from you? – Andras Sebestyen Aug 31 at 11:57 1 @Andras Sebestyen I tried my best to explain it. Make sense?

– PhpMyCoder Aug 31 at 12:41 I am struggling with this too. What if my query looks like "SELECT * FROM contactgroups ORDER BY $sortfield"? – Col.

Shrapnel Aug 31 at 12:49 Thanks very much indeed. Here is the first explanation that was clear and understandable. (I wish everybody would have a sense to make explanations easy like this.) Thanks again and now I am going to do my homework:))) – Andras Sebestyen Aug 31 at 13:31 1 @Andras Sebestyen htmlspecialchars() should only be used on output that originally came from the user (like comments, display names, etc).

Things like passwords and emails don't need it because they should never be outputted on a page. – PhpMyCoder Aug 31 at 14:42.

Your PHP code is commented You're not calling echo You're using $one instead of $result You're not sanitizing the input, which is not causing this problem but it is something that should be fixed. Sanitizing is as easy as $g_id = intval( $_GET'gid' );, but I advise looking into prepared statements. Having said that, this should work: query("SELECT * FROM contactgroups WHERE id=".

$g_id); $result = $one->fetch();? > Rename groupname.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.